package com.sunwayworld.basemodule.business.result.io;

import com.alibaba.fastjson.JSONObject;
import com.sunwayworld.basemodule.basedata.exporttemplate.service.LimsGenericTemplateService;
import com.sunwayworld.basemodule.business.ordertask.bean.LimsOrderTaskBean;
import com.sunwayworld.basemodule.business.ordertask.service.LimsOrderTaskService;
import com.sunwayworld.basemodule.business.result.bean.LimsResultBean;
import com.sunwayworld.basemodule.business.result.service.LimsResultService;
import com.sunwayworld.basemodule.coredata.test.bean.LimsPossibleResultBean;
import com.sunwayworld.framework.constant.Constant;
import com.sunwayworld.framework.data.page.Page;
import com.sunwayworld.framework.io.excel.utils.ExcelUtils;
import com.sunwayworld.framework.restful.data.RestJsonWrapperBean;
import com.sunwayworld.framework.spring.annotation.GikamBean;
import com.sunwayworld.framework.utils.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Path;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component(value = "LimsResultExportTemplate")
@GikamBean
public class LimsResultExportTemplate implements LimsGenericTemplateService<String> {

    @Autowired
    @Lazy
    private LimsResultService resultService;


    @Autowired
    @Lazy
    private LimsOrderTaskService orderTaskService;

    @Override
    public void initTemplateData(Path path, String id) {
        if (ObjectUtils.isEmpty(id)) {
            return;
        }
        try {
            InputStream in = new FileInputStream(path.toString());
            Workbook workBook = WorkbookFactory.create(in);
            Sheet sheet = workBook.getSheetAt(0);
            Map<Integer, List<LimsPossibleResultBean>> possibleMap = new HashMap<>();
            Page<LimsResultBean> resultColumnPage = resultService.selectResultToColumns(NumberUtils.parseLong(id), new RestJsonWrapperBean());
            if (!resultColumnPage.getRows().isEmpty()) {
                //补充动态列
                addResultColumns(sheet.getRow(0), workBook, resultColumnPage.getRows(), possibleMap);
            }
            RestJsonWrapperBean orderTaskWrapperBean = new RestJsonWrapperBean();
            orderTaskWrapperBean.setFilterValue("runId_EQ", id);
            orderTaskWrapperBean.setFilterValue("status_SB", "logged");
            orderTaskWrapperBean.setFilterValue("flag_EQ", "1");
            orderTaskWrapperBean.removePageParams();
//            Page<LimsOrderTaskBean> orderTaskBeanPage = orderTaskService.selectOrderTaskWithResult(orderTaskWrapperBean);
            Page<LimsOrderTaskBean> orderTaskBeanPage = orderTaskService.selectOrderTaskWithOriginalResult(orderTaskWrapperBean);
            if (!orderTaskBeanPage.getRows().isEmpty()) {
                //补充数据
                addInfo(sheet, workBook, orderTaskBeanPage.getRows(), possibleMap);
            }
            in.close();
            FileOutputStream outputStream = new FileOutputStream(path.toString());
            workBook.write(outputStream);
            outputStream.close();
            workBook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //*********************************************************************
    //私有方法
    //*********************************************************************
    private void addResultColumns(Row row, Workbook wb, List<LimsResultBean> resultColumnList, Map<Integer, List<LimsPossibleResultBean>> possibleMap) {
        int endCellNum = row.getPhysicalNumberOfCells();
        CellStyle csForTitle = ExcelUtils.getTitleCellStyle(wb); // 子标题和列标题的样式
        csForTitle.setBorderTop(BorderStyle.THIN);
        CellStyle csForTitleRequired = ExcelUtils.getTitleCellStyle(wb);// 必填字段样式
        Font font = wb.createFont();
        font.setColor(Font.COLOR_RED);
        csForTitleRequired.setFont(font);
        csForTitleRequired.setBorderTop(BorderStyle.THIN);
        DataFormat dataFormat = wb.createDataFormat();
        csForTitle.setDataFormat(dataFormat.getFormat("@"));
        csForTitleRequired.setDataFormat(dataFormat.getFormat("@"));
        //创建绘图对象
        Drawing<?> p = row.getSheet().createDrawingPatriarch();
        for (int i = 0; i < resultColumnList.size(); i++) {
            LimsResultBean result = resultColumnList.get(i);
            Cell cell = row.createCell(endCellNum + i);
            addComment(p, cell, result.getAnalyteId().toString());
            if (!StringUtils.isEmpty(result.getExt$Item("possibleresultinfo"))) {
                List<LimsPossibleResultBean> possibleResultBeans = JSONObject.parseArray(result.getExt$Item("possibleresultinfo"), LimsPossibleResultBean.class);
                possibleMap.put(endCellNum + i, possibleResultBeans);
            }
            String analyte = result.getAnalyte();
            if("D".equalsIgnoreCase(result.getAnalType())) {
                analyte += "(yyyy-MM-dd hh:mm)";
            }
            if (StringUtils.equals(result.getWriteFlag(), Constant.YES)) {
                cell.setCellValue(analyte + "*");
                cell.setCellStyle(csForTitleRequired);
            } else {
                cell.setCellValue(analyte);
                cell.setCellStyle(csForTitle);
            }
        }
    }

    private void addInfo(Sheet sheet, Workbook wb, List<LimsOrderTaskBean> orderTaskList, Map<Integer, List<LimsPossibleResultBean>> possibleMap) {
        CellStyle leftCellStyle = ExcelUtils.getLeftCellStyle(wb);
        DataFormat dataFormat = wb.createDataFormat();
        leftCellStyle.setDataFormat(dataFormat.getFormat("@"));
        int endRowNum = sheet.getPhysicalNumberOfRows();
        Row firstRow = sheet.getRow(0);
        int endCellNum = firstRow.getPhysicalNumberOfCells();
        //获取列信息：
        String[] columnNames = new String[endCellNum];
        for (int i = 0; i < endCellNum; i++) {
            Cell cell = firstRow.getCell(i);
            columnNames[i] = cell.getCellComment().getString().getString();
        }
        for (int i = 0; i < orderTaskList.size(); i++) {
            LimsOrderTaskBean orderTask = orderTaskList.get(i);
            Row row = sheet.createRow(i + endRowNum);
            Cell orderNoCell = row.createCell(0);
            orderNoCell.setCellStyle(leftCellStyle);
            orderNoCell.setCellValue(i + endRowNum);
            for (int j = 1; j < columnNames.length; j++) {
                Cell cell = row.createCell(j);
                String columnName = columnNames[j];
                // ext$中的字段也可以用属性名直接获取（但注意批注是小写）
                String value = ObjectUtils.toString(BeanUtils.getPropertyValue(orderTask, columnName));
                if (ArrayUtils.asList("sampletype").contains(columnName)) {
                    value = value;
                }
                cell.setCellValue(value);
                cell.setCellStyle(leftCellStyle);
            }
        }
        //设置下拉框
        possibleMap.keySet().forEach(cellNum -> {
            List<LimsPossibleResultBean> possibleResultBeans = possibleMap.get(cellNum);
            String[] strs = possibleResultBeans.stream().map(LimsPossibleResultBean::getOriginalResult).distinct().toArray(String[]::new);
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(strs);
            //参数顺序：开始行、结束行、开始列、结束列
            CellRangeAddressList regions = new CellRangeAddressList(endRowNum, orderTaskList.size() + endRowNum - 1, cellNum, cellNum);
            DataValidation data = helper.createValidation(constraint, regions);
            data.setSuppressDropDownArrow(true);
            data.setShowErrorBox(true);
//            data.setSuppressDropDownArrow(false);
            sheet.addValidationData(data);
        });
    }

    /**
     * 设置单元格注释
     *
     * @param patriarch
     * @param cell
     */
    public void addComment(Drawing patriarch, Cell cell, String note) {

        Comment comment = patriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 1, 2, (short) 4, 4));

        comment.setString(new XSSFRichTextString(note));

        cell.setCellComment(comment);

    }
}
